from flask import Flask
import pymysql

app = Flask(__name__)

# MySQL 数据库连接配置
DB_HOST = 'localhost'
DB_USER = 'root'
DB_PASSWORD = '010615'
DB_NAME = 'qxyt2'


# 创建用户表
def create_user_table():
    conn = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                      id INT AUTO_INCREMENT PRIMARY KEY,
                      username VARCHAR(50) UNIQUE NOT NULL,
                      password VARCHAR(50) NOT NULL)''')
    conn.commit()
    cursor.close()
    conn.close()


# 插入用户
def insert_user(username, password):
    conn = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''INSERT INTO users (username, password) VALUES (%s, %s)''', (username, password))
    conn.commit()
    cursor.close()
    conn.close()


# 删除用户
def delete_user(user_id):
    conn = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''DELETE FROM users WHERE id = %s''', (user_id,))
    conn.commit()
    cursor.close()
    conn.close()


# 获取所有用户
def get_all_users():
    conn = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''SELECT id, username, password FROM users''')
    users = cursor.fetchall()
    cursor.close()
    conn.close()
    return users


# 创建用户表格
def create_user_table_html(users):
    table = "<table border='1'><tr><th>ID</th><th>Username</th><th>Password</th><th>Action</th></tr>"
    for user in users:
        table += "<tr>"
        table += f"<td>{user[0]}</td>"
        table += f"<td>{user[1]}</td>"
        table += f"<td>{user[2]}</td>"
        table += f"<td><a href='/delete/{user[0]}'>Delete</a></td>"
        table += "</tr>"
    table += "</table>"
    return table